from lin.interface import InfoCrud as Base
from sqlalchemy import Column, DECIMAL, Integer, Enum, CHAR, DATE, DATETIME, VARCHAR, SMALLINT, TEXT, Table, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base


metadata = declarative_base().metadata


class MandTor(Base):
    """
    融资方信息记录表
    """
    __tablename__ = 'zfae_mandator'
    sn = Column(SMALLINT, primary_key=True, autoincrement=True, index=True, comment='编号')
    name = Column(VARCHAR(64), comment='融资方名称')
    credit = Column(DECIMAL(25, 10), nullable=False, default=0, comment='授信额度')
    used = Column(DECIMAL(25, 10), nullable=False, default=0, comment='已完成募集额度')
    remain = Column(DECIMAL(25, 10), nullable=False, default=0, comment='剩余未募集募集额度')
    status = Column(Enum('未募资', '募集中', '已完成'), nullable=False, comment='状态')
    remark = Column(TEXT, comment='备注')


class brokerage(Base):
    """
    承销商记录表，记录承销商名称，若平台存在相同承销商
    """
    __tablename__ = 'zfae_brokerage'
    sn = Column(SMALLINT, primary_key=True, autoincrement=True, index=True, comment='编号')
    name = Column(VARCHAR(64), comment='承销商名称')
    status = Column(Enum('有效', '失效'), default='有效', nullable=False, comment='状态')


class client(Base):
    """
    用户信息表，若平台存在则关联相关SN，同时记录该用户所属渠道以及交易所
    """
    __tablename__ = 'zfae_client'
    sn = Column(SMALLINT, primary_key=True, autoincrement=True, index=True, comment='编号')
    srcSn = Column(VARCHAR(6), nullable=False, default='0', comment='平台Sn，0表示未平台用户')
    userName = Column(VARCHAR(64), nullable=False, comment='姓名')
    cardNo = Column(VARCHAR(32), nullable=False, comment='身份证号')
    phone = Column(VARCHAR(11), comment='手机号')
    type = Column(Enum('个人', '企业'), comment='用户类型')
    registTime = Column(DATETIME, comment='注册时间')


class clientbank(Base):
    """
    客户银行卡信息表，记录用户以及银行卡相关信息，
    """
    __tablename__ = 'zfae_clientbank'
    sn = Column(SMALLINT, primary_key=True, autoincrement=True, index=True, comment='编号')
    userSn = Column(SMALLINT, ForeignKey('zfae_client.sn'), default='0', nullable=False, comment='用户Sn')
    cardNo = Column(CHAR(24), nullable=False, comment='卡号')
    status = Column(Enum('有效', '未认证', '无效'), default='未认证', nullable=False, comment='状态')
    assetType = Column(Enum('基础账户', '登记账户'), default='基础账户', comment='账户名称')
    pId = Column(SMALLINT, nullable=False, default='0', comment='变更后的银行卡ID，0表示为原始信息')


class clientasset(Base):
    __tablename__ = 'zfae_clientasset'
    sn = Column(SMALLINT, primary_key=True, autoincrement=True, index=True, comment='编号')
    userSn = Column(SMALLINT, ForeignKey('zfae_client.sn'), default='0', nullable=False, comment='用户ID')
    availableBalance = Column(DECIMAL(25, 10), default='0.0000', nullable=False, comment='可用资金')
    frozenWithDraw = Column(DECIMAL(25, 10), default='0.0000', nullable=False, comment='提现冻结资金')
    frozenPay = Column(DECIMAL(25, 10), default='0.0000', nullable=False, comment='交易冻结资金')
    frozenTrans = Column(DECIMAL(25, 10), default='0.0000', nullable=False, comment='转让冻结资金')
    assetType = Column(Enum('基础账户', '登记账户'), default='基础账户', comment='账户名称,不存在则建立，存在则更新')


class clientcashdetail(Base):
    """
    用户账户资金交易记录表
    """
    __tablename__ = 'zfae_clientcashdetail'
    sn = Column(SMALLINT, primary_key=True, autoincrement=True, index=True, comment='编号')
    userSn = Column(SMALLINT, ForeignKey('zfae_client.sn'), default='0', nullable=False, comment='用户ID')
    orderNo = Column(CHAR(32), default='0', nullable=False, comment='平台交易SN,0表示非平台用户')
    cardNo = Column(CHAR(8), nullable=True, comment='卡号, 充值和提现的时候填写')
    incom = Column(DECIMAL(25, 10), default='0.0000', comment='入金金额')
    pay = Column(DECIMAL(25, 10), default='0.0000', nullable=False, comment='出金金额')
    type = Column(Enum('购买', '转让', '手续费', '还款', '成立放款', '融资方还款', '代偿'), nullable=False, comment='交易类型')
    assetType = Column(Enum('基础账户', '登记账户'), default='基础账户', comment='账户名称')


class clientposion(Base):
    """
    持仓表
    """
    __tablename__ = 'zfae_clientposion'
    sn = Column(SMALLINT, primary_key=True, autoincrement=True, index=True, comment='编号')
    userSn = Column(SMALLINT, ForeignKey('zfae_client.sn'), default='0', nullable=False, comment='用户SN')
    productSn = Column(SMALLINT, ForeignKey('zfae_product.sn'), default='0', nullable=False, comment='产品Sn')
    rateSequence = Column(VARCHAR(512), nullable=False, comment='利率序列，用来区分同一产品不同仓位')
    quota = Column(Integer, nullable=False, comment='认购数量')
    quantity = Column(Integer, nullable=False, comment='当前持仓量')
    parentRateSequence = Column(VARCHAR(512), comment='如果产品利率变更，则需要生成新的持仓，并将原序列持仓做相应处理，0表示原生ID')
    assetType = Column(Enum('基础账户', '登记账户'), comment='资产所属账户')


class clientrepment(Base):
    """
    用户还款计划表
    """
    __tablename__ = 'zfae_clientrepment'
    sn = Column(SMALLINT, primary_key=True, autoincrement=True, index=True, comment='编号')
    clientPosionSn = Column(SMALLINT, ForeignKey('zfae_clientposion.sn'), nullable=False, comment='还款对应持仓')
    productRepaySn = Column(SMALLINT, ForeignKey('zfae_productrepay.sn'), nullable=False, comment='产品还款计划Sn')
    yieldRate = Column(DECIMAL(25, 10), nullable=False, comment='计息利率')
    calcCapital = Column(Integer, nullable=False, comment='计息本金')
    capitalBlance = Column(DECIMAL(25, 10), nullable=False, comment='还款本金')
    yieldBlance = Column(DECIMAL(25, 10), nullable=False, comment='还款收益')
    status = Column(Enum('未还款', '已还款'), nullable=False, comment='当前还款状态')
    type = Column(Enum('清算', '部分', '转让'), nullable=False, comment='还款类型')


class product(Base):
    """
    产品表
    """
    __tablename__ = 'zfae_product'
    sn = Column(SMALLINT, primary_key=True, autoincrement=True, index=True, comment='编号')
    RSn = Column(VARCHAR(6), default='0', nullable=False, comment='平台Sn,0表示非平台用户')
    name = Column(VARCHAR(64), nullable=False, comment='产品名称')
    status = Column(Enum('已成立', '部分清算', '已清算'), nullable=False, comment='当前产品状态')
    mandatorSn = Column(SMALLINT, ForeignKey("zfae_mandator.sn"), nullable=False, comment='融资方代码')
    bookrunnerSn = Column(SMALLINT, ForeignKey("zfae_brokerage.sn"), nullable=False, comment='承销商代码')
    assetType = Column(Enum('基础账户', '登记账户'), comment='账户名称')


class productinfo(Base):
    """
    产品附属信息表
    """
    __tablename__ = 'zfae_productinfo'
    sn = Column(SMALLINT, primary_key=True, autoincrement=True, index=True, comment='编号')
    productSn = Column(SMALLINT, ForeignKey('zfae_product.sn'), default='0', nullable=False, comment='产品Sn')
    quota = Column(Integer, nullable=False, comment='募集额度')
    unitvalue = Column(DECIMAL(25, 10), nullable=False, default='1.00', comment='单价')
    daysofyear =Column(Enum('360', '365'), default='360', comment='一年计息天数')
    valueStartDate = Column(DATE, nullable=False, comment='起息时间')
    ValueEndDate = Column(DATE, nullable=False, comment='止息时间')
    settmentDate = Column(DATE, nullable=False, comment='清算时间')
    duration = Column(SMALLINT, nullable=False, comment='计息天数')
    precision = Column(SMALLINT, nullable=False, default='2', comment='保留小数位，默认为2位')
    mode = Column(Enum('舍尾', '四舍五入'), nullable=False, default='舍尾', comment='计息方式，默认为四舍五入')
    onlineType = Column(Enum('线上产品', '线下产品'), default='线上产品', nullable=False, comment='线上线下标签')
    productType = Column(Enum('首发产品', '再发产品'), nullable=False, comment='记录产品是否为原始产品')
    pSn = Column(VARCHAR(6), comment='再发产品对应的原始产品SN')


class productrepay(Base):
    """
    产品还款计划表
    """
    __tablename__ = 'zfae_productrepay'
    sn = Column(SMALLINT, primary_key=True, autoincrement=True, index=True, comment='编号')
    productSn = Column(SMALLINT, ForeignKey('zfae_product.sn'), default='0', nullable=False, comment='产品Sn')
    valueStartDate = Column(DATE, nullable=False, comment='起息时间')
    ValueEndDate = Column(DATE, nullable=False, comment='止息时间')
    settmentDate = Column(DATE, nullable=False, comment='清算时间')
    duretion = Column(SMALLINT, nullable=False, comment='本期计息天数')
    type = Column(Enum('到期清算', '派息清算', '赎回清算'), nullable=False, comment='清算类型')


class bankorder(Base):
    """
    银行流水记录表，记录银行流水
    """
    __tablename__ = 'zfae_bankorder'
    sn = Column(Integer, primary_key=True, autoincrement=True, index=True, comment='编号')
    cardNo = Column(CHAR(24), nullable=True, comment='卡号, 充值和提现的时候填写')
    userName = Column(VARCHAR(64), nullable=False, comment='姓名')
    amount = Column(DECIMAL(25, 10), nullable=False, comment='交易金额')
    fee = Column(DECIMAL(25, 10), nullable=False, default='0', comment='手续费')
    channel = Column(Enum('渤海', '邮储', 'POS', '登记', '其他'), nullable=False, comment='入金渠道')
    ocTime = Column(DATETIME, nullable=False, comment='交易时间')

"""
还款统计视图
"""
t_sens_repayment_view = Table(
    'sens_repayment_view', metadata,
    Column('capital', DECIMAL(47, 10)),
    Column('yield', DECIMAL(47, 10)),
    Column('date_time', String(10))
)